#coding=utf-8
__author__ = 'huangzhi'

import simplejson
import jinja2
# import werkzeug.utilsfrom openerp import http
from openerp.http import request

from openerp.addons.web import http
# from opernerp.http import Response
# from openerp.addons.web.http import request
# from openerp.addons.eroad_mweb.controllers import mweb_public
from openerp import SUPERUSER_ID
import datetime

import urllib
# from eroad_odoo_utils import eroad_utils
# from openerp.addons import eroad_qywechat_api
# from openerp.addons import eroad_gzwechat_api
# from eroad_odoo_utils.eroad_wechat_util import qywechat_sdk, gzwechat_sdk,jssdk_sign
# from eroad_bracelet_web import _get_wx_sdk_jsapi_conf as _get_wx_sdk_jsapi_conf

# from openerp.addons.eroad_qywechat_api import conf
import codecs

import itertools

import logging

_logger = logging.getLogger(__name__)


loader = jinja2.PackageLoader('openerp.addons.eroad_bracelet_web_json', 'html')
env = jinja2.Environment(loader=loader, autoescape=True)
env.filters["json"] = simplejson.dumps

class doctor_patient_group_list_json(http.Controller):
    #鎵€鏈夋偅鑰呯殑鍒楄〃
    @http.route('/doctor/all/patient/list', type='http', auth="user", cors='*')
    def doctor_patient_list(self, req, **kw):
        method = req.httprequest.method
        if True:
            pn = req.params.get('pn')
            if (pn==''): pn = 0
            if (pn is None): pn=0
            pn = int(pn)
            if pn<=0: pn = 1

            ps = req.params.get('ps')
            if (ps==''): ps = 0
            if (ps is None): ps = 0
            ps = int(ps)
            if ps <=0: ps = 10

            did = req.params.get('did')
            if (did is None): did = -1
            v = int(did)

            gid = req.params.get('gid')
            if (gid is None): gid = -1
            gid = int(gid)

            pc = 0

            registry = req.registry

            #鍒嗛〉 begin
            sql = """
                    select count(1)
                    from eroad_bracelet_patient a
                    left join eroad_bracelet_doctor_patient_rel d on a.id=d.patient_id
                    left join eroad_bracelet_doctor e on e.id=d.doctor_id
                    left join res_partner f on a.partner_id=f.id
                    left join res_partner g on e.partner_id=g.id
                    left join res_country_state h on f.state_id=h.id
                    where d.doctor_id=%s
                  """ % (did)
            with registry.cursor() as cr:
                cr.execute(sql)
                pc = cr.dictfetchall()[0]["count"]

            if pc < pn *ps:
                pn = int(pc / ps) + 1

                # pc = len(doc_pat_rel_ids)
            # sql = ("select a.patient_id,b.* from eroad_bracelet_doctor_patient_rel a left join eroad_bracelet_patient b on a.patient_id=b.id where a.doctor_id=%s order by a.patient_id limit %s OFFSET %s"  % (docid, ps,(pn-1)*ps))
            sql1 = """
                    select d.doctor_id,d.patient_id, g.name as dname,f.name as pname,coalesce(h.name,'') as state_name,f.city from eroad_bracelet_patient a
                    left join eroad_bracelet_doctor_patient_rel d on a.id=d.patient_id
                    left join eroad_bracelet_doctor e on e.id=d.doctor_id
                    left join res_partner f on a.partner_id=f.id
                    left join res_partner g on e.partner_id=g.id
                    left join res_country_state h on f.state_id=h.id
                    where d.doctor_id=%s %(docid) order by d.patient_id
                    """ % (did)

            if pn<=1:
                sql = sql1 + sql
            # print sql
            with registry.cursor() as cr:
                cr.execute(sql)
                doc_patient_lists = cr.dictfetchall()
            # print pc, pn, ps, doc_patient_lists
            _logger.info( 'sql:%s' % sql)
            patientjson = ''
            for patient in doc_patient_lists:
                _logger.info( 'doctor_id:%s' % patient['did'])
                # print patient["id"], patient["nickname"]
                name = patient['name'] if patient['name'] else patient['nickname']   #淇敼鎮ｈ€呯殑鍚嶇О  mazh
                temp = "{'id':'%s','did':'%s','name':'%s','headimgurl':'%s', 'country':'%s','state':'%s','city':'%s','isbind':'%s'}" \
                       % (patient['patient_id'], patient['did'], name, patient['headimgurl'], patient['country_name'],
                          patient['state_name'], patient['city'], patient['isbind'])
                # print temp
                if patientjson!='':
                    patientjson = patientjson + ',' + temp
                else:
                    patientjson = temp

            success = 'true'
            html = ''
            # html = "{'success':'%s','totalcount':'%s','pn':'%s','ps':'%s','title': '鎴戠殑鎮ｈ€�','header':'鎮ｈ€呭垪琛�'," \
            #        "'docid':'%s','data':[%s]}" % (success, pc, pn, ps, docid, patientjson)

            return request.make_response(html,{
                    'Cache-Control': 'no-cache',
                    'Content-Type': 'text/html; charset=utf-8',
                    'Access-Control-Allow-Origin': '*',
                    'Origin': '*',
                    'Access-Control-Allow-Methods': 'GET,POST',
                    'Access-Control-Allow-Headers': 'Referer, Accept, Origin, User-Agent'
                        })
        else:
            return "{%s}" % (method)


    #2鎮ｈ€呭垎缁� http://localhost:48269/doctor/patient/groups/list?did=31
    @http.route('/doctor/patient/groups/list', type='http', auth="user", cors='*')
    def doctor_patient_list(self, req, **kw):
        method = req.httprequest.method
        if True:

            did = req.params.get('did')
            if (did is None): did = -1
            did = int(did)

            pc = 0

            registry = req.registry

            #鏌愬尰鐢熺殑鎮ｈ€呮€绘暟
            sql = """
                    select count(*) as dpc from eroad_bracelet_doctor_patient_rel where doctor_id=%s
                  """ % (did)
            with registry.cursor() as cr:
                cr.execute(sql)
                dpc = cr.dictfetchall()[0]["dpc"]
            #鍒嗙粍鎬荤殑鎮ｈ€呮暟
            sql = """
                    select sum(pc) as ppc from (select distinct doctor_id,patient_id, case when patient_id is null then 0 else 1 end as pc from patient_group  where doctor_id=%s) t
                  """ % (did)
            with registry.cursor() as cr:
                cr.execute(sql)
                ppc = cr.dictfetchall()[0]["ppc"]

            #鍒嗛〉 begin
            sql = """
                    select count(*) from (select id from patient_group where doctor_id=%s group by id,name order by id) t
                  """ % (did)
            with registry.cursor() as cr:
                cr.execute(sql)
                pc = cr.dictfetchall()[0]["count"]

            sql = """
                    select id,name, doctor_id,sum(pc) as pc from patient_group where doctor_id=%s group by id,name,doctor_id order by id
                    """ % (did)
            with registry.cursor() as cr:
                cr.execute(sql)
                doc_patient_groups = cr.dictfetchall()

            templist = []
            temp = {
                'gid':-1,
                'name':'鍏ㄩ儴',
                'did':did,
                'pc':dpc
            }
            templist.append(temp)
            if doc_patient_groups.__len__()>0:
                temp = {
                    'gid':0,
                    'name':'鏈垎缁�',
                    'did':did,
                    'pc':dpc-ppc
                }
                templist.append(temp)
            for group in doc_patient_groups:
                temp = {
                    'gid':group['id'],
                    'name':group['name'],
                    'did':did,
                    'pc':group['pc'],

                }
                templist.append(temp)



            success = True

            html = {
                    "success":success,
                    "title": "鎮ｈ€呭垎缁�",
                    "header":"鎮ｈ€呭垎缁�",
                    "pc":pc,
                    "data":templist
                    }

            html = simplejson.dumps(html)

            return request.make_response(html,{
                    'Cache-Control': 'no-cache',
                    'Content-Type': 'text/html; charset=utf-8',
                    'Access-Control-Allow-Origin': '*',
                    'Origin': '*',
                    'Access-Control-Allow-Methods': 'GET,POST',
                    'Access-Control-Allow-Headers': 'Referer, Accept, Origin, User-Agent'
                        })
        else:
            return "{%s}" % (method)



    #鏍规嵁鍖荤敓缂栧彿鍜屾偅鑰呯紪鍙峰彇澶囨敞鏈鏁�
    def doctor_get_unread(self, req, did, pid):
        #鎮ｈ€呬俊鎭湭璇绘潯鏁�
        try:
            doctor_id = int(did)
            patient_id = int(pid)

            weekly_line_obj = req.registry['weekly.report.comment.line']
            weekly_comment_obj = req.registry['weekly.report.comment']
            psqi_line_obj = req.registry['psqi.report.comment.line']
            psqi_comment_obj = req.registry['psqi.report.comment']
            weekly_line_unread = len(weekly_line_obj.search(req.cr,SUPERUSER_ID,
                                                            [
                                                                ('is_read','=',False),
                                                                ('line_type','=','patient'),
                                                                ('doctor_id','=',doctor_id),
                                                                ('patient_id','=',patient_id)
                                                            ]))
            weekly_comment_unread = len(weekly_comment_obj.search(req.cr,SUPERUSER_ID,
                                                                  [
                                                                      ('is_read','=',False),
                                                                      ('sponsor','=',2),
                                                                      ('doctor_id','=',doctor_id),
                                                                      ('patient_id','=',patient_id)
                                                                  ]))
            psqi_comment_unread = len(psqi_comment_obj.search(req.cr,SUPERUSER_ID,
                                                              [
                                                                  ('is_read','=',False),
                                                                  ('sponsor','=',2),
                                                                  ('doctor_id','=',doctor_id),
                                                                  ('patient_id','=',patient_id)
                                                              ]))
            psqi_line_unread = len(psqi_line_obj.search(req.cr,SUPERUSER_ID,
                                                        [
                                                            ('is_read','=',False),
                                                            ('line_type','=','patient'),
                                                            ('doctor_id','=',doctor_id),
                                                            ('patient_id','=',patient_id)
                                                        ]))
            unreads = weekly_comment_unread + weekly_line_unread + psqi_line_unread + psqi_comment_unread
            return unreads
        except Exception,e:
            return 0

    #3鏌愬尰鐢熺殑鏌愪釜鍒嗙粍鐨勬偅鑰呭垪琛�
    #(鍏ㄩ儴)   http://localhost:48269/doctor/patient/agroups/list?did=31&gid=-1
    #(鏈垎缁�) http://localhost:48269/doctor/patient/agroups/list?did=31&gid=0
    #(鏌愬垎缁�) http://localhost:48269/doctor/patient/agroups/list?did=31&gid=5
    @http.route('/doctor/patient/agroups/list', type='http', auth="user", cors='*')
    def doctor_patient_agroups_list(self, req, **kw):
        method = req.httprequest.method
        if True:

            did = req.params.get('did')
            if (did is None): did = -1
            did = int(did)

            gid = req.params.get('gid')
            if (gid is None): gid = -1
            gid = int(gid)

            pn = req.params.get('pn')
            if (pn==''): pn = 0
            if (pn is None): pn=0
            pn = int(pn)
            if pn<=0: pn = 1

            ps = req.params.get('ps')
            if (ps==''): ps = 0
            if (ps is None): ps = 0
            ps = int(ps)
            if ps <=0: ps = 10

            # print did,gid

            pc = 0

            registry = req.registry

            #锛�1鍏ㄩ儴 0鏈垎缁� n琛ㄧず鏌愬垎缁�
            csql = """
                    select count(*) as count from eroad_bracelet_patient a
                    left join eroad_bracelet_doctor_patient_rel d on a.id=d.patient_id
                    left join eroad_bracelet_doctor e on e.id=d.doctor_id
                    left join res_partner f on a.partner_id=f.id
                    left join res_partner g on e.partner_id=g.id
                    left join res_country_state h on f.state_id=h.id
                    where d.doctor_id = %s
                      and d.patient_id in (
                        select coalesce(patient_id,0) from patient_group where doctor_id=%s and id=%s
                      )
                """ % (did, did, gid)
            if gid == -1:
                csql = """
                        select count(*) as count from eroad_bracelet_doctor_patient_rel where doctor_id=%s
                    """ % (did)
            if gid == 0:
                csql = """
                        select count(*) as count from eroad_bracelet_patient a
                        left join eroad_bracelet_doctor_patient_rel d on a.id=d.patient_id
                        left join eroad_bracelet_doctor e on e.id=d.doctor_id
                        left join res_partner f on a.partner_id=f.id
                        left join res_partner g on e.partner_id=g.id
                        left join res_country_state h on f.state_id=h.id
                        where d.doctor_id = %s
                          and d.patient_id not in (
                            select coalesce(patient_id,0) from patient_group where doctor_id=%s
                          )
                    """ % (did, did)
            with registry.cursor() as cr:
                cr.execute(csql)
                pc = cr.dictfetchall()[0]["count"]

            # select d.doctor_id,d.patient_id, g.name as dname,f.name as pname,coalesce(h.name,'') as state_name,f.city,a.headimgurl,coalesce(s.eroad_bracelet_patient_id, -1) as isbind,coalesce(j.groups,'') as groups from eroad_bracelet_patient a
            # left join eroad_bracelet_doctor_patient_rel d on a.id=d.patient_id
            # left join eroad_bracelet_doctor e on e.id=d.doctor_id
            # left join res_partner f on a.partner_id=f.id
            # left join res_partner g on e.partner_id=g.id
            # left join res_country_state h on f.state_id=h.id
            # left join (select eroad_bracelet_patient_id from eroad_bracelet_users group by eroad_bracelet_patient_id)
            # s on a.id=s.eroad_bracelet_patient_id
            # left join patient_groups j on j.patient_id=a.id
            # where d.doctor_id = %s
            #   and d.patient_id in (
            #     select coalesce(patient_id,0) from patient_group where doctor_id=%s and id=%s
            #   )
            # order by d.patient_id
            sql = """
                    select a.doctor_id,a.patient_id,a.name as dname,coalesce(f.name,'') as pname,'' as state_name,'' as city,b.headimgurl,
                    coalesce(s.eroad_bracelet_patient_id, -1) as isbind,coalesce(j.groups,'') as groups
                    from patient_group a
                    left join eroad_bracelet_patient b on b.id=a.patient_id
                    left join res_partner f on b.partner_id=f.id
                    left join (select distinct eroad_bracelet_patient_id from eroad_bracelet_users where active=true)
                                        s on b.id=s.eroad_bracelet_patient_id
                    left join patient_groups j on j.patient_id=a.patient_id and j.doctor_id=a.doctor_id
                    where a.doctor_id=%s and a.id=%s
                    order by a.patient_id limit %s OFFSET %s
                    """ % (did,gid,ps,(pn-1)*ps)
            if gid==-1:
                sql = """
                        select d.doctor_id,d.patient_id, g.name as dname,coalesce(f.name,'') as pname,coalesce(h.name,'') as state_name,f.city,a.headimgurl,coalesce(s.eroad_bracelet_patient_id, -1) as isbind,coalesce(j.groups,'') as groups from eroad_bracelet_patient a
                        left join eroad_bracelet_doctor_patient_rel d on a.id=d.patient_id
                        left join eroad_bracelet_doctor e on e.id=d.doctor_id
                        left join res_partner f on a.partner_id=f.id
                        left join res_partner g on e.partner_id=g.id
                        left join res_country_state h on f.state_id=h.id
                        left join (select distinct eroad_bracelet_patient_id from eroad_bracelet_users where active=true)
                        s on a.id=s.eroad_bracelet_patient_id
                        left join patient_groups j on j.patient_id=a.id and j.doctor_id=%s
                        where d.doctor_id=%s
                        order by d.patient_id limit %s OFFSET %s
                      """ % (did, did,ps,(pn-1)*ps)
            if gid==0:
                sql = """
                        select d.doctor_id,d.patient_id, g.name as dname,coalesce(f.name,'') as pname,coalesce(h.name,'') as state_name,f.city,a.headimgurl,coalesce(s.eroad_bracelet_patient_id, -1) as isbind,coalesce(j.groups,'') as groups from eroad_bracelet_patient a
                        left join eroad_bracelet_doctor_patient_rel d on a.id=d.patient_id
                        left join eroad_bracelet_doctor e on e.id=d.doctor_id
                        left join res_partner f on a.partner_id=f.id
                        left join res_partner g on e.partner_id=g.id
                        left join res_country_state h on f.state_id=h.id
                        left join (select distinct eroad_bracelet_patient_id from eroad_bracelet_users where active=true)
                        s on a.id=s.eroad_bracelet_patient_id
                        left join patient_groups j on j.patient_id=a.id and j.doctor_id=%s
                        where d.doctor_id = %s
                          and d.patient_id not in (
                            select coalesce(patient_id,0) from patient_group where doctor_id=%s
                          )
                        order by d.patient_id limit %s OFFSET %s
                        """ % (did,did,did,ps,(pn-1)*ps)
            with registry.cursor() as cr:
                cr.execute(sql)
                doc_patient_agroups_list = cr.dictfetchall()

            templist = []
            for group in doc_patient_agroups_list:
                temp = {
                    'did':group['doctor_id'],
                    'pid':group['patient_id'],
                    'dname':group['dname'],
                    'pname':group['pname'],
                    'state':group['state_name'],
                    'city':group['city'],
                    'groups':group['groups'],
                    "headimgurl":group['headimgurl'],
                    "isbind":group['isbind'],
                    "unread":self.doctor_get_unread(req, group['doctor_id'], group['patient_id'])
                }
                templist.append(temp)
            #sorted(templist,key=lambda s: s['unread'],reverse=True)
            templist = sorted(templist ,key = lambda x:(x['unread'],x['pid']),reverse=True)
            #templist.sort( 'unread',reverse=Ture)
            success = True

            html = {
                    "success":success,
                    "title": "鎮ｈ€呭垎缁勫垪琛�",
                    "header":"鎮ｈ€呭垎缁勫垪琛�",
                    "pc":pc,
                    "data":templist
                    }

            html = simplejson.dumps(html)

            return request.make_response(html,{
                    'Cache-Control': 'no-cache',
                    'Content-Type': 'text/html; charset=utf-8',
                    'Access-Control-Allow-Origin': '*',
                    'Origin': '*',
                    'Access-Control-Allow-Methods': 'GET,POST',
                    'Access-Control-Allow-Headers': 'Referer, Accept, Origin, User-Agent'
                        })
        else:
            return "{%s}" % (method)

    #4 灏嗗尰鐢熺殑鏌愭偅鑰呯殑鏌愪釜鍒嗙粍鍒犻櫎
    #(鍏ㄩ儴)   http://localhost:48269/doctor/patient/delete/group?did=31&gid=1&pid=1542
    @http.route('/doctor/patient/delete/group/', type='http', auth="user", cors='*')
    def doctor_patient_delete_group(self, req, **kw):
        method = req.httprequest.method
        if True:

            did = req.params.get('did')
            if (did is None): did = -1
            did = int(did)

            pid = req.params.get('pid')
            if (pid is None): pid = -1
            pid = int(pid)

            gid = req.params.get('gid')
            if (gid is None): gid = -1
            gid = int(gid)

            registry = req.registry

            sql = """
                    delete from patientgroup_rel where id=%s and patient_id=%s
                    """ % (gid,pid)

            with registry.cursor() as cr:
                cr.execute(sql)
                cr.commit()

            success = True

            html = {
                    "success":success,
                    "title": "鍒犻櫎鎮ｈ€呯殑鍒嗙粍",
                    "header":"鍒犻櫎鎮ｈ€呯殑鍒嗙粍"
                    }

            html = simplejson.dumps(html)

            return request.make_response(html,{
                    'Cache-Control': 'no-cache',
                    'Content-Type': 'text/html; charset=utf-8',
                    'Access-Control-Allow-Origin': '*',
                    'Origin': '*',
                    'Access-Control-Allow-Methods': 'GET,POST',
                    'Access-Control-Allow-Headers': 'Referer, Accept, Origin, User-Agent'
                        })
        else:
            return "{%s}" % (method)

    #5 鍒犻櫎鏌愪釜鍒嗙粍 鎰忔€濆氨鏄繖涓垎缁勭殑鎮ｈ€呭叏閮ㄥ垹闄�
    #(鍏ㄩ儴)   http://localhost:48269/doctor/patient/delete/agroups?gid=1
    @http.route('/doctor/patient/delete/agroups/', type='http', auth="user", cors='*')
    def doctor_patient_delete_agroups(self, req, **kw):
        method = req.httprequest.method
        if True:
            gid = req.params.get('gid')
            if (gid is None): gid = -1
            gid = int(gid)

            registry = req.registry

            sql = """
                    delete from patientgroup_rel where id=%s
                    """ % (gid)

            with registry.cursor() as cr:
                cr.execute(sql)
                cr.commit()

            sql = """
                    delete from patientgroup where id=%s
                    """ % (gid)

            with registry.cursor() as cr:
                cr.execute(sql)
                cr.commit()

            success = True

            html = {
                    "success":success,
                    "title": "鍒犻櫎鎮ｈ€呯殑鍒嗙粍",
                    "header":"鍒犻櫎鎮ｈ€呯殑鍒嗙粍"
                    }

            html = simplejson.dumps(html)

            return request.make_response(html,{
                    'Cache-Control': 'no-cache',
                    'Content-Type': 'text/html; charset=utf-8',
                    'Access-Control-Allow-Origin': '*',
                    'Origin': '*',
                    'Access-Control-Allow-Methods': 'GET,POST',
                    'Access-Control-Allow-Headers': 'Referer, Accept, Origin, User-Agent'
                        })
        else:
            return "{%s}" % (method)

    #6 灏嗘墍閫夋偅鑰呮坊鍔犲埌鏌愪釜鍒嗙粍
    #(鍏ㄩ儴)   http://localhost:48269/doctor/patient/add/agroups
    @http.route('/doctor/patient/agroups/add', type='json', auth="user", cors='*')
    def doctor_patient_agroups_add(self, req, **kw):
        method = req.httprequest.method
        if True:
            json_req = simplejson.loads(req.httprequest.data)
            print "1",json_req
            did = json_req['did']
            gid = json_req['gid']
            gname = json_req['gname']
            print "2", gname

            if not gid and not gname:
                success = 'false'
                html = '{"success":"%s"}' % (success)
                return html

            patient_ids = list(json_req.get('pid',[]))
            patient_ids = [int(patient_id) for patient_id in patient_ids]

            registry = req.registry
            patientgroup_obj = registry['patientgroup']
            ids = patientgroup_obj.search(req.cr, SUPERUSER_ID, [('name','=',gname),('doctorid','=',did)])
            if len(ids)>0:
                ret = ids[0]
            else:
                if (gid):
                    ret = gid
                else:
                    vals = {
                        'name':gname,
                        'doctorid':did
                    }
                    ret = patientgroup_obj.create(req.cr, SUPERUSER_ID, vals)
                    req.cr.commit()

            for patient_id in patient_ids:
                sql = """
                        insert into patientgroup_rel(id,patient_id) values(%s,%s)
                        """ % (ret, patient_id)
                with registry.cursor() as cr:
                    cr.execute(sql)
            req.cr.commit()

            success = 'true'

            html = '{"success":"%s"}' % (success)
            return html
        else:
            return "{%s}" % (method)

    #7 鏌愬尰鐢熺殑闄や簡鏌愪釜鍒嗙粍浠ュ鐨勬偅鑰呭垪琛�
    #(鏌愬垎缁�) http://localhost:48269/doctor/patient/unagroups/list?did=31&gid=1
    @http.route('/doctor/patient/unagroups/list', type='http', auth="user", cors='*')
    def doctor_patient_unagroups_list(self, req, **kw):
        method = req.httprequest.method
        if True:

            did = req.params.get('did')
            if (did is None): did = -1
            did = int(did)

            gid = req.params.get('gid')
            if (gid is None): gid = -1
            gid = int(gid)

            pc = 0

            registry = req.registry

            #锛�1鍏ㄩ儴 0鏈垎缁� n琛ㄧず鏌愬垎缁�
            csql = """
                    select count(*) as count from eroad_bracelet_patient a
                    left join eroad_bracelet_doctor_patient_rel d on a.id=d.patient_id
                    left join eroad_bracelet_doctor e on e.id=d.doctor_id
                    left join res_partner f on a.partner_id=f.id
                    left join res_partner g on e.partner_id=g.id
                    left join res_country_state h on f.state_id=h.id
                    where d.doctor_id = %s
                      and d.patient_id not in (
                        select coalesce(patient_id,0) from patient_group where doctor_id=%s and id=%s
                      )

                """ % (did, did, gid)

            with registry.cursor() as cr:
                cr.execute(csql)
                pc = cr.dictfetchall()[0]["count"]

            sql = """
                    select d.doctor_id,d.patient_id, g.name as dname,f.name as pname,coalesce(h.name,'') as state_name,f.city,a.headimgurl,coalesce(s.eroad_bracelet_patient_id, -1) as isbind,coalesce(j.groups,'') as groups from eroad_bracelet_patient a
                    left join eroad_bracelet_doctor_patient_rel d on a.id=d.patient_id
                    left join eroad_bracelet_doctor e on e.id=d.doctor_id
                    left join res_partner f on a.partner_id=f.id
                    left join res_partner g on e.partner_id=g.id
                    left join res_country_state h on f.state_id=h.id
                    left join (select distinct eroad_bracelet_patient_id from eroad_bracelet_users where active=true)
                    s on a.id=s.eroad_bracelet_patient_id
                    left join patient_groups j on j.patient_id=a.id and j.doctor_id=%s
                    where d.doctor_id = %s
                      and d.patient_id not in (
                        select coalesce(patient_id,0) from patient_group where doctor_id=%s and id=%s
                      )
                    order by d.patient_id
                    """ % (did,did,did,gid)

            with registry.cursor() as cr:
                cr.execute(sql)
                doc_patient_agroups_list = cr.dictfetchall()

            templist = []
            for group in doc_patient_agroups_list:
                temp = {
                    'did':group['doctor_id'],
                    'pid':group['patient_id'],
                    'dname':group['dname'],
                    'pname':group['pname'],
                    'state':group['state_name'],
                    'city':group['city'],
                    'groups':group['groups'],
                    "headimgurl":group['headimgurl'],
                    "isbind":group['isbind'],
                    "unread":self.doctor_get_unread(req, group['doctor_id'], group['patient_id'])
                }
                templist.append(temp)

            success = True

            html = {
                    "success":success,
                    "title": "鎮ｈ€呭垎缁勫垪琛�",
                    "header":"鎮ｈ€呭垎缁勫垪琛�",
                    "pc":pc,
                    "data":templist
                    }

            html = simplejson.dumps(html)

            return request.make_response(html,{
                    'Cache-Control': 'no-cache',
                    'Content-Type': 'text/html; charset=utf-8',
                    'Access-Control-Allow-Origin': '*',
                    'Origin': '*',
                    'Access-Control-Allow-Methods': 'GET,POST',
                    'Access-Control-Allow-Headers': 'Referer, Accept, Origin, User-Agent'
                        })
        else:
            return "{%s}" % (method)

    #8 灏嗘墍閫夋偅鑰呯殑鏌愪釜鍒嗙粍杩涜鏇存柊
    #(鍏ㄩ儴)   http://localhost:48269/doctor/patient/agroups/update?did=31&gid=2&
    @http.route('/doctor/patient/agroups/update', type='json', auth="user", cors='*')
    def doctor_patient_agroups_update(self, req, **kw):
        method = req.httprequest.method
        if True:
            json_req = simplejson.loads(req.httprequest.data)
            did = json_req['did']
            gid = json_req['gid']
            gname = json_req['gname']

            if not gid and not gname:
                success = 'false'
                html = '{"success":"%s"}' % (success)
                return html



            patient_ids = list(json_req.get('pid',[]))
            patient_ids = [int(patient_id) for patient_id in patient_ids]

            registry = req.registry
            patientgroup_obj = registry['patientgroup']

            #鍏堝垽鏂�
            # print gname, did
            ids = patientgroup_obj.search(req.cr, SUPERUSER_ID, [('name','=',gname),('doctorid','=',did)])
            if len(ids)>0 and int(ids[0])!=int(gid):#琛ㄧず鍖荤敓鐨勬爣绛惧瓨鍦紝鍒欏悎骞舵偅鑰�
                # print "琛ㄧず鍖荤敓鐨勬爣绛惧瓨鍦紝鍒欏悎骞舵偅鑰�",ids[0],gid
                for patient_id in patient_ids:
                    sql = """
                            select * from patientgroup_rel where id=%s and patient_id=%s
                        """ % (ids[0], patient_id)
                    with registry.cursor() as cr:
                        cr.execute(sql)
                        patientgroup_rel_lists = cr.dictfetchall()
                    if patientgroup_rel_lists.__len__()<=0:
                        try:
                            sql = """
                                    insert into patientgroup_rel(id,patient_id) values(%s,%s)
                                    """ % (ids[0], patient_id)
                            with registry.cursor() as cr:
                                cr.execute(sql)
                        except Exception,e:
                            pass
                    sql = """
                            delete from patientgroup_rel where id=%s
                            """ % (gid)
                    with registry.cursor() as cr:
                        cr.execute(sql)
                    sql = """
                            delete from patientgroup where id=%s and doctorid=%s
                            """ % (gid, did)
                    with registry.cursor() as cr:
                        cr.execute(sql)
                req.cr.commit()
            else:
                pass
                #鏇存柊鍒嗙粍鍚嶇О
                vals = {
                    'name':gname
                }
                ret = patientgroup_obj.write(req.cr, SUPERUSER_ID, int(gid), vals)
                req.cr.commit()

                sql = """
                        delete from patientgroup_rel where id=%s
                        """ % (gid)
                with registry.cursor() as cr:
                    cr.execute(sql)

                for patient_id in patient_ids:
                    sql = """
                            insert into patientgroup_rel(id,patient_id) values(%s,%s)
                            """ % (gid, patient_id)
                    with registry.cursor() as cr:
                        cr.execute(sql)
                req.cr.commit()

            success = 'true'

            html = '{"success":"%s"}' % (success)
            return html
        else:
            return "{%s}" % (method)


    #9 灏嗘墍閫夋偅鑰呯殑鏌愪釜鍒嗙粍杩涜鏇存柊
    #(鍏ㄩ儴)   http://localhost:48269/doctor/patient/agroups/update/bak?did=31&gid=2&
    @http.route('/doctor/patient/agroups/update/bak', type='json', auth="user", cors='*')
    def doctor_patient_agroups_update_bak(self, req, **kw):
        method = req.httprequest.method
        if True:
            json_req = simplejson.loads(req.httprequest.data)
            did = json_req['did']
            gid = json_req['gid']
            gname = json_req['gname']

            if not gid and not gname:
                success = 'false'
                html = '{"success":"%s"}' % (success)
                return html



            patient_ids = list(json_req.get('pid',[]))
            patient_ids = [int(patient_id) for patient_id in patient_ids]

            registry = req.registry
            patientgroup_obj = registry['patientgroup']

            #鍏堝垽鏂�
            ids = patientgroup_obj.search(req.cr, SUPERUSER_ID, [('name','=',gname),('doctorid','=',did)])

            #鏇存柊鍒嗙粍鍚嶇О
            vals = {
                'name':gname
            }
            ret = patientgroup_obj.write(req.cr, SUPERUSER_ID, int(gid), vals)
            req.cr.commit()

            sql = """
                    delete from patientgroup_rel where id=%s
                    """ % (gid)
            with registry.cursor() as cr:
                cr.execute(sql)

            for patient_id in patient_ids:
                sql = """
                        insert into patientgroup_rel(id,patient_id) values(%s,%s)
                        """ % (gid, patient_id)
                with registry.cursor() as cr:
                    cr.execute(sql)
            req.cr.commit()

            success = 'true'

            html = '{"success":"%s"}' % (success)
            return html
        else:
            return "{%s}" % (method)